package com.cnooc.zyfx;


import com.cnooc.zyfx.entity.Entities;
import com.cnooc.zyfx.entity.Entities.queryKPI;
import com.github.thorqin.toolkit.Application;
import com.github.thorqin.toolkit.annotation.Service;
import com.github.thorqin.toolkit.db.DBService;
import com.github.thorqin.toolkit.db.DBService.DBTable;
import com.github.thorqin.toolkit.validation.annotation.Validate;
import com.github.thorqin.toolkit.web.annotation.*;
import com.github.thorqin.toolkit.web.router.WebContent;
import com.github.thorqin.toolkit.web.session.WebSession;
import com.github.thorqin.toolkit.utility.Serializer;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.joda.time.DateTime;

@WebModule(value="/api")
public class ValueModule {

   // @Service("db")
   // DBService db;
	
	@Service("application")
    Application app;
	
    @Service("sqlServer")
    DBService msdb;
    
    public static class Obj {
		public String key;
		public String value;
		
	}
    
    
    private String makeSql(String sql, Entities.queryAnalysisInput input, List<Object> args,Integer judge) {
    	if(judge==1){//queryValue
    		if (input.sectionId != null) {
        		sql += " and b.BuzBoardCode=?";
        		args.add(input.sectionId);
        	}else{
        		sql += " and b.BuzBoardCode in (?)";
        		args.add(input.sections);
        	}
    		if (input.KPIs != null && input.KPIs.size() > 0) {
        		sql += " and k.FieldCode in (?)";
        		args.add(input.KPIs);
        	}
        	if (input.companyId != null && input.companyId.size() > 0) {
        		sql += " and v.CompCode in (?)";
        		args.add(input.companyId);
        	}
    	}else{//queryhistory
    		if (input.sectionId != null) {
        		sql += " and BuzBoardCode=?";
        		args.add(input.sectionId);
        	}else{
        		sql += " and BuzBoardCode in (?)";
        		args.add(input.sections);
        	}
    		if (input.KPIs != null && input.KPIs.size() > 0) {
        		sql += " and FieldCode in (?)";
        		args.add(input.KPIs);
        	}
        	if (input.companyId != null && input.companyId.size() > 0) {
        		sql += " and CompCode in (?)";
        		args.add(input.companyId);
        	}
    	}
    	    	
    	//	默认显示当前年
	    if (input.yearTime == null) {
    		sql += " and ReportDate = DATENAME(YEAR,GETDATE())  ";
    	   // sql += " and ReportYear = 2014 ";
	    }else{
    		sql += " and ReportYear =  ? ";
    		args.add(input.yearTime);
    	}
    	return sql;
    }
     
    /*comments functions*/
    @WebEntry(method = HttpMethod.POST)
    public DBTable queryValue(@Entity Entities.queryAnalysisInput input) throws SQLException, IOException {
        try (DBService.DBSession session = msdb.getSession(false)) {
        	String initsql = "select #FIELD# from MDPrdKPI k INNER JOIN"
        			+ " MDBuzBoard b ON k.BuzBoardCode = b.BuzBoardCode INNER JOIN"
        			+ "  B_Probenchmarkvalue v ON k.FieldCode = v.FieldCode INNER JOIN "
        			+ " MDPrdComp c ON b.BuzBoardCode = c.BuzBoardCode AND v.CompCode = c.CompCode INNER JOIN"
        			+ " v_KPI_best_value bv on v.FieldCode=bv.FieldCode AND v.CompCode = bv.CompCode  "
        			+ " where 1=1 ";
        	String pageBegin = "select top 10 * from (";
        	String pageEnd = ") t where num > ?";
        	String fieldList = "0 checked, ROW_NUMBER() over(order by ReportDate) as num,"
        			+ "v.ID, v.ReportDate, v.CompCode, v.FieldCode, v.ReportYear,"
        			+ " v.CompReference, v.InduReference, v.BenchmarkValue, v.CreateTime,"
        			+ " v.CreateUser, v.AuditUser, v.AuditTIme, v.UpdateTime, k.FiledName,"
        			+ " b.BuzBoardName, c.DeptName,v.user_id, k.FieldUnit, b.BuzBoardCode,  "
        			+ "case when v.UpdateTime is null then v.CreateTime else v.UpdateTime end lastEditTime,  "
        			+ "case when v.UpdateUser is null then v.CreateUser else v.UpdateUser end lastEditUser, bv.svalue,v.Remark  ";
        	List<Object> args = new LinkedList<>();
        	String sql = makeSql(initsql,input, args,1);
        	String querySql = pageBegin + sql.replace("#FIELD#", fieldList) + pageEnd;
        	String countSql = sql.replace("#FIELD#", "count(0) as ct");
        	
        	int len = (Integer)session.queryFirst(countSql, args.toArray()).get("ct");
        	args.add(10 * (input.page-1));
            DBTable tb = session.queryTable(querySql,args.toArray());
            tb.length = len;
            return tb;
        }
    }
    
    
    @WebEntry(method=HttpMethod.POST)
    public DBTable queryHistory(@Entity Entities.queryAnalysisInput input) throws SQLException {
    	try(DBService.DBSession session = msdb.getSession(false)){
    		String initsql="select #FIELD# from B_Probenchmarkvalue_History where 1=1 ";
    		String pageBegin = "select top 10 * from (";
        	String pageEnd = ") tt where num > ?";
        	String fieldList = "0 checked, ROW_NUMBER() over(order by ReportDate desc) as num,"
        			+ "* ";
        	List<Object> args = new LinkedList<>();
        	String sql = makeSql(initsql,input, args,0);
        	String querySql = pageBegin + sql.replace("#FIELD#", fieldList) + pageEnd;
        	String countSql = sql.replace("#FIELD#", "count(0) as ct");
        	
        	int len = (Integer)session.queryFirst(countSql, args.toArray()).get("ct");
        	args.add(10 * (input.page-1));
            DBTable tb = session.queryTable(querySql,args.toArray());
            tb.length = len;
            return tb;
    	}
    }
    
    @WebEntry(method = HttpMethod.POST)
    public void modifyValue(@Entity Entities.modifyValueInput input,WebSession websession) throws SQLException, IOException {
	    try (DBService.DBSession session = msdb.getSession(false)) { 
	    	session.execute(app.readScript("sql/modifyValue.sql"),input.value ,input.remark,
	    			new Date(),(String)websession.get("uid"),(String)websession.get("name"),input.id );
	    	String u=input.history.init_userId;
	    	Boolean t=( u == "");
	    	Boolean r=( u == null);
	    	 if(u == null ||"".equals(u)){
	    		session.execute(app.readScript("sql/addValueHistory.sql"), input.history.ReportDate, input.history.ComCode, input.history.CompName,
		    			input.history.FieldCode,input.history.FieldName,input.history.FieldUnit,input.history.BuzBoardCode,input.history.BestValue,
		    			input.history.ReportYear,input.history.initBenchMarkValue,input.history.initRemark,null,
		    			null,input.history.initUpdateTime);
	    	}
	    	session.execute(app.readScript("sql/addValueHistory.sql"), input.history.ReportDate, input.history.ComCode, input.history.CompName,
	    			input.history.FieldCode,input.history.FieldName,input.history.FieldUnit,input.history.BuzBoardCode,input.history.BestValue,
	    			input.history.ReportYear,input.history.BenchMarkValue,input.history.Remark,input.history.user_id,
	    			input.history.UpdateUser,input.history.UpdateTime);
	    	session.commit();
	    }
	}
}

